from dataclasses import dataclass
from flask import request, Blueprint
from sqlalchemy import  Engine, text
from dotenv import load_dotenv
import os
from qcloud_cos import CosConfig, CosS3Client
import json


engine: Engine
def make_engine(debug: bool):
    from sqlalchemy import create_engine
    engine_path = (
        "postgresql://Administrator@localhost/jwtest"
        if debug
        else "postgresql://jw238:123123@localhost/main"
    )
    print(f"[engine use] {engine_path}")
    engine: Engine = create_engine(engine_path)
    return engine


LOCAL_TEST = False
TABLE_NAME = "musics"
BP_musics = Blueprint("BP_musics", __name__, url_prefix="/musics")

# 加载.env文件中的环境变量
load_dotenv()
BUCKET = "zq1-1318886512"
COS_SECRET_ID = os.environ['COS_SECRET_ID']
COS_SECRET_KEY = os.environ['COS_SECRET_KEY']
COS_REGION = os.environ['COS_REGION']

config = CosConfig(Region=COS_REGION, SecretId=COS_SECRET_ID, SecretKey=COS_SECRET_KEY, Token=None, Scheme='https')
client = CosS3Client(config)

def init():
    if not LOCAL_TEST:
        BP_musics.add_url_rule("/dl", view_func=getMusicDLurl, methods=["GET"])
        BP_musics.add_url_rule("/info", view_func=getMusicINFO, methods=["GET"])
        BP_musics.add_url_rule("/page", view_func=getMusicPage, methods=["GET"])
        ...

    with engine.begin() as conn:
        sql = f"""
        SELECT tablename FROM pg_tables WHERE schemaname = 'public' AND tablename = '{TABLE_NAME}';
        """
        result = conn.execute(text(sql))
        tables = result.fetchall()
        if len(tables) == 0:
            sql = f"""
            CREATE TABLE {TABLE_NAME} (
                id SERIAL PRIMARY KEY,
                name VARCHAR(256) NOT NULL,
                bv VARCHAR(12),
                UNIQUE (bv)
            );
            CREATE UNIQUE INDEX musics_bv ON {TABLE_NAME} (bv);
            """
            conn.execute(text(sql))


def getMusicDLurl():
    bv = request.args.get("bv", None) if not LOCAL_TEST else "BV1Uv4y1u7Gj"
    with engine.begin() as conn:
        sql = f"""
        SELECT name FROM {TABLE_NAME} WHERE bv = :bv;
        """
        result = conn.execute(text(sql), {
            "bv": bv
        })
        if result.rowcount == 0:
            return "Not Found", 404
        
        name = result.fetchone()[0]
        # print("bv = result.fetchone()[0]", bv)

        url = client.get_presigned_url(
            Method='GET',
            Bucket=BUCKET,
            Key=f'/musics/{name}.mp3',
            Params={
                'response-content-disposition': f'attachment;filename={name}.mp3' # 下载时保存为指定的文件
            }, 
            Expired=60,
            SignHost=True
        )
        return url
    
@dataclass
class Music:
    id: int
    name: str
    bv: str

def getMusicPage():
    LIMIT = 6
    page = request.args.get("page", None) if not LOCAL_TEST else 1
    with engine.begin() as conn:
        sql = f"""
        SELECT * FROM {TABLE_NAME} LIMIT {LIMIT} OFFSET {LIMIT * (page - 1)};
        """
        result = conn.execute(text(sql))
        res = result.fetchall()
        if len(res) == 0:
            return "Not Found", 404
        
        res = [Music(id=row[0], name=row[1], bv=row[2]) for row in res]
        res = json.dumps([row.__dict__ for row in res])
        return res, 200
    

def getMusicINFO():
    bv = request.args.get("bv", None) if not LOCAL_TEST else "BV1Uv4y1u7Gj"
    with engine.begin() as conn:
        sql = f"""
        SELECT * FROM {TABLE_NAME} WHERE bv = :bv;
        """
        result = conn.execute(text(sql), {
            "bv": bv
        })
        if result.rowcount == 0:
            return "Not Found", 404
        
        one = result.fetchone()
        one = Music(id=one[0], name=one[1], bv=one[2])
        # print("bv = result.fetchone()[0]", bv)

    url = client.get_presigned_url(
        Method='GET',
        Bucket=BUCKET,
        Key=f'/covers/{one.name}.png',
        Expired=60,
    )
    return {
        "name": one.name,
        "bv": one.bv,
        "cover_url": url
    }, 200
    

if __name__ == "__main__":
    LOCAL_TEST = True
    init()
    # print(getMusicDLurl())
    # print(getMusicPage())
    print(getMusicINFO())